﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.RMM;

namespace yunbo.Repositorys.RMMRepositorys
{
    public class Rm_yclcgxqRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public int Addyclcgxq(Rm_yclcgxq rm_Yclcgxq)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"insert into [dbo].[rm_yclcgxq](yclcgxq_code, yclcg_code, wl_code, wl_name,yclcg_sl, yclcg_dw, yclcg_dj, yclcg_xj, bz, yclcgxq_ydhsl)
                                values('{rm_Yclcgxq.yclcgxq_code}','{rm_Yclcgxq.yclcg_code}','{rm_Yclcgxq.wl_code}','{rm_Yclcgxq.wl_name}','{rm_Yclcgxq.yclcg_sl}','{rm_Yclcgxq.yclcg_dw}','{rm_Yclcgxq.yclcg_dj}','{rm_Yclcgxq.yclcg_xj}','{rm_Yclcgxq.bz}','{rm_Yclcgxq.yclcgxq_ydhsl}')";
                int cgxq = connection.Execute(sql);
                return cgxq;

            }
        }
        public Rm_yclcgxq GetRm_Yclcgxq(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[rm_yclcgxq] where [yclcg_code]='{code}'";
                Rm_yclcgxq rm_Yclcgxq=connection.Query<Rm_yclcgxq>(sql).FirstOrDefault();
                return rm_Yclcgxq;
            }
        }
        public int UpDh(decimal ydhsl, string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"update [dbo].[rm_yclcgxq] set [yclcgxq_ydhsl]='{ydhsl}' where [yclcg_code]='{code}'";
                int dh = connection.Execute(sql);
                return dh;
            }
        }
        public List<Rm_yclcgxq> Getrk(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select 
	                        xq.[wl_code],
	                        xq.[wl_name],
	                        xq.[yclcgxq_ydhsl],
	                        wl.[wl_fl],
	                        fl.dictionary_name,
	                        wl.[wl_dw],
	                        dw.dw_name,
	                        cg.yclcg_gys,
	                        ks.[ks_name],
	                        cg.rkzt
	                        from [dbo].[rm_yclcgxq] xq
	                        LEFT JOIN 
	                        [dbo].[rm_yclcg]  as cg
	                        ON xq.yclcg_code=cg.yclcg_code
	                        LEFT JOIN
	                        [dbo].[bd_wl] AS wl
	                        ON xq.wl_code=wl.wl_code
	                        LEFT JOIN 
	                        [dbo].[dictionary] AS fl
	                        ON fl.dictionary_id=wl.wl_fl
	                        LEFT JOIN 
	                        [dbo].[bd_dw] AS dw
	                        ON dw.[dw_id]=wl.wl_dw
	                        LEFT JOIN 
	                        [dbo].[bd_ks] AS ks
	                        ON ks.ks_code=cg.yclcg_gys
                            where xq.[yclcg_code]='{code}'";
                List<Rm_yclcgxq> rk = connection.Query<Rm_yclcgxq>(sql).ToList();
                return rk;
            }
        }
        public List<Rm_yclcgxq> Getdh(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select 
	                        xq.[wl_code],
	                        xq.[wl_name],
	                        xq.[yclcgxq_ydhsl],
	                        wl.[wl_fl],
	                        fl.dictionary_name,
	                        wl.[wl_dw],
	                        dw.dw_name,
	                        cg.yclcg_gys,
	                        ks.[ks_name],
	                        cg.rkzt,
							xq.yclcg_sl,
							xq.yclcgxq_ydhsl
	                        from [dbo].[rm_yclcgxq] xq
	                        LEFT JOIN 
	                        [dbo].[rm_yclcg]  as cg
	                        ON xq.yclcg_code=cg.yclcg_code
	                        LEFT JOIN
	                        [dbo].[bd_wl] AS wl
	                        ON xq.wl_code=wl.wl_code
	                        LEFT JOIN 
	                        [dbo].[dictionary] AS fl
	                        ON fl.dictionary_id=wl.wl_fl
	                        LEFT JOIN 
	                        [dbo].[bd_dw] AS dw
	                        ON dw.[dw_id]=wl.wl_dw
	                        LEFT JOIN 
	                        [dbo].[bd_ks] AS ks
	                        ON ks.ks_code=cg.yclcg_gys
                            where xq.[yclcg_code]='{code}'";
                List<Rm_yclcgxq> rk = connection.Query<Rm_yclcgxq>(sql).ToList();
                return rk;
            }
        }
		public int Upgrade(UpRm_yclcgxq upRm_Yclcgxq)
		{
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"update [dbo].[rm_yclcgxq] set[yclcg_sl]='{upRm_Yclcgxq.yclcg_sl}',[yclcg_xj]='{upRm_Yclcgxq.yclcg_xj}',[bz]='{upRm_Yclcgxq.bz}'
                                where [yclcg_code]='{upRm_Yclcgxq.yclcg_code}'";
                int sh = connection.Execute(sql);
                return sh;
            }
        }
    }
}
